using System.Collections.Generic;
using Microsoft.Data.Sqlite;
using 第六次作业.entity;

namespace 第六次作业.utils.crud;

public class SchoolRepository
{
    public void Create(School school)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            List<School> schools = new List<School>();
            schools = GetAll();
            int num = schools.Count;
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "INSERT INTO School (Id, Name) VALUES (@Id, @Name)";
            command.Parameters.AddWithValue("@Name", school.Name);
            command.Parameters.AddWithValue("@Id", num + 1);
            command.ExecuteNonQuery();
        }
    }

    public void Delete(int id)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "DELETE FROM School WHERE Id = @Id";
            command.Parameters.AddWithValue("@Id", id);
            command.ExecuteNonQuery();
        }
    }

    public void Update(School school)
    {
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "UPDATE School SET Name = @Name WHERE Id = @Id";
            command.Parameters.AddWithValue("@Name", school.Name);
            command.Parameters.AddWithValue("@Id", school.Id);
            command.ExecuteNonQuery();
        }
    }

    public List<School> GetAll()
    {
        List<School> schools = new List<School>();
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM School";
            SqliteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                School school = new School
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1)
                };
                schools.Add(school);
            }
        }
        return schools;
    }

    public School GetById(int id)
    {
        List<School> schools = new List<School>();
        using (SqliteConnection connection = DatabaseUtil.GetConnection())
        {
            connection.Open();
            SqliteCommand command = connection.CreateCommand();
            command.CommandText = "SELECT * FROM School WHERE Id = @Id";
            command.Parameters.AddWithValue("@Id", id);
            SqliteDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                School school = new School
                {
                    Id = reader.GetInt32(0),
                    Name = reader.GetString(1)
                };
                schools.Add(school);
            }
        }
        return schools[0];
    }
}